package dao;

import java.util.*;
import java.sql.*;

import model.*;

public class UserDao{
	
    private static UserDao userDao= new UserDao();

    
    private UserDao() {
        
    }
    
    public static UserDao getInstance() {
        return userDao;
    }
    
    
  //添加加用户
    public boolean insertUser(User user) {
        String sql="insert into U(uid,password,name,sex,age,nationality,fromCountry,id)"
        		+ "values(?,?,?,?,?,?,?,?)";
        Connection conn = BaseConn.getConn();
        PreparedStatement pstm=null;
        try {
            pstm = conn.prepareStatement(sql);
            pstm.setObject(1, user.getUid());
            pstm.setObject(2, user.getPassword());
            pstm.setObject(3, user.getName());
            pstm.setObject(4, user.getSex());
            pstm.setObject(5, user.getAge());
            pstm.setObject(6, user.getNationality());
            pstm.setObject(7, user.getFromCountry());
            pstm.setObject(8, user.getId());
            return pstm.executeUpdate()>0;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                pstm.close();
                conn.close();
                
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }
    
    public User queryUser(int uid, String password) {
        String sql = "select uid,name,password from U where uid = ?";
        if (password != null) sql += " and password = ?";//针对管理员,登陆时账号密码都需要，adm查询时只需要账号
        Connection conn = BaseConn.getConn();
        ResultSet res = null;
        User user = null;
        PreparedStatement pstm = null;
        try {
            pstm = conn.prepareStatement(sql);
            pstm.setObject(1, uid);
            if (password != null) pstm.setObject(2, password);
            res=pstm.executeQuery();
            while(res.next()){
                int return_uid = res.getInt("uid");
                String name = res.getString("name");
                String return_password = res.getString("password");
                user = new User(return_uid,return_password ,name);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{
            try {
            		res.close();
            		pstm.close();
            		conn.close();
                
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return user;
    }
    
 //adm查看所有用户
    public Object[][] queryAllUser() {
        String sql = "select * from U";
        List<User> users=new ArrayList<User>();
        Connection conn = BaseConn.getConn();
        //创建一个结果集
        ResultSet res = null;
        PreparedStatement pstm = null;
        try {
            pstm=conn.prepareStatement(sql);
            //执行
            res=pstm.executeQuery();
            while(res.next()){
                int uid=res.getInt("uid");
                String password=res.getString("password");
                String name=res.getString("name");
                String sex=res.getString("sex");
                int age=res.getInt("age");
                String nation=res.getString("nationality");
                String from=res.getString("fromCountry");
                String id=res.getString("id");
                User user=new User(uid,password,name,sex,age,nation,from,id);
                users.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{
            try {
            	res.close();  
            	pstm.close();   
            	conn.close();
               
               
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
      Object obj[][]=new Object[users.size()][8];
      int i=0;
      for(User user: users) {       //forreach语句
    	  obj[i][0]=user.getUid();
    	  obj[i][1]=user.getPassword();
          obj[i][2]=user.getName();
          obj[i][3]=user.getSex();
    	  obj[i][4]=user.getAge();
    	  obj[i][5]=user.getNationality();
    	  obj[i][6]=user.getFromCountry();
    	  obj[i][7]=user.getId();
    		i++	;														 
    		}
      return obj;
    }
    
    //删除用户
    public boolean delUser(int uid) {
        String sql="delete from U where uid="+uid;
        Connection conn = BaseConn.getConn();
        PreparedStatement pstm = null;
        try {
            pstm = conn.prepareStatement(sql);
            return pstm.executeUpdate()>0;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                pstm.close();
                conn.close();
                
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }
    
    //修改用户名
    /*public boolean updUserEvery(User user) {
    	 String sql = "update U set ";
         if (user.getPassword() != null) {
             sql += "password="+user.getPassword() + ",";
         }
         if (user.getName() != null) {
             sql += "discount="+user.getName() + ",";
         }
         if (user.getSex() != null) {
             sql += "sex="+user.getSex() + ",";
         }
         if (user.getAge() != 0) {
             sql += "age='"+user.getAge() + "',";
         }
         if (user.getNationality() != null) {
             sql += "nationality='"+user.getNationality() + "' ,";
         }
         if (user.getFromCountry() != null) {
             sql += "fromCountry='"+user.getFromCountry() + "' ,";
         }
         if(user.getId()!=null) {
        	 sql+="id='"+user.getId()+"',";
         }
         sql = sql.substring(0, sql.length() - 1); //把多出来的','减去
         sql += " where uid=" + user.getUid() + "";
         
         System.out.println(sql);
         
         Connection conn=BaseConn.getConn();
         PreparedStatement pstm = null;
         try {
             pstm = conn.prepareStatement(sql);
             int res = pstm.executeUpdate();
             if(res > 0) return true;
         } catch (SQLException e) {
             e.printStackTrace();
         } finally{
             try {
             	pstm.close();
             	conn.close(); 
             } catch (SQLException e) {
                 e.printStackTrace();
             }
         }
         return false;
    }*/
    
    //修改用户密码
   public boolean updUserPassword(int uid, String password) {
        String sql = "update U set password=? where uid="+uid;
        Connection conn = BaseConn.getConn();
        PreparedStatement pstm=null;
        try {
            pstm = conn.prepareStatement(sql);
            pstm.setObject(1, password);
            return pstm.executeUpdate()>0;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{
            try {
            	pstm.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }
}